package com.tongji.run;

import com.ibeetl.admin.core.util.DateUtil;
import com.tongji.run.manager.ManagerFactory;
import org.beetl.sql.core.*;
import org.beetl.sql.core.db.DBStyle;
import org.beetl.sql.core.db.MySqlStyle;
import org.beetl.sql.ext.DebugInterceptor;

import java.io.File;
import java.util.*;

public class 回访按照最后采浆日期分配 {

    public static String dateStart = "2017-01-01 00:00:00";
    public static String dateEnd = "2018-12-31 23:59:59";

    public static Date dateRangeStart = DateUtil.getDate(dateStart, "yyyy-MM-dd HH:mm:ss");
    public static Date dateRangeEnd = DateUtil.getDate(dateEnd, "yyyy-MM-dd HH:mm:ss");
    public static String dateRange = DateUtil.format(dateRangeStart, "yyyy年MM月dd日") + "至" + DateUtil.format(dateRangeEnd, "yyyy年MM月dd日");
    public static String collectionNumberStart = "2";
    public static String collectionNumberEnd = "5";
    public static String root = "E:\\文档\\2023\\回访记录\\超半年回访记录\\" + dateRange + "(次数：" + collectionNumberStart + "-" + collectionNumberEnd + ")";

    public static void main(String[] args) {
        ConnectionSource source = ConnectionSourceHelper.getSimple("com.mysql.cj.jdbc.Driver", "jdbc:mysql://127.0.0.1:3308/tongji?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false&useInformationSchema=true", "root", "123456");
        DBStyle mysql = new MySqlStyle();
        // sql语句放在classpagth的/sql 目录下
        SQLLoader loader = new ClasspathLoader("/sql");
        // 数据库命名跟java命名一样，所以采用DefaultNameConversion，还有一个是UnderlinedNameConversion，下划线风格的，
        UnderlinedNameConversion nc = new  UnderlinedNameConversion();
        // 最后，创建一个SQLManager,DebugInterceptor 不是必须的，但可以通过它查看sql执行情况
        SQLManager sqlManager = new SQLManager(mysql,loader,source,nc,new Interceptor[]{new DebugInterceptor()});


        SQLManager taibangSqlManager = ManagerFactory.taibangSqlManager();

        List tongjiList = new ArrayList();

        File rootFile = new File(root);
        rootFile.mkdirs();
        //if(true) return ;
        List memberList = sqlManager.execute(new SQLReady("select * from member where keshi != '退休' and name != '孙莉莉'", new Object[] {}), HashMap.class);
        List<HashMap> donorChangeList = new ArrayList();
        List<HashMap> donorList = taibangSqlManager.execute(new SQLReady("select * from (\n" +
                "select d.donorNo 浆员编号, \n" +
                "d.donorName 浆员姓名,\n" +
                "d.phone 联系电话, \n" +
                "quxian.cname as 区县名称,\n" +
                "area.cname as 区域名称,d.codeDonorState as 状态,\n" +
                "STR_TO_DATE(right(left(d.idCard,14),8),\"%Y%m%d\") as 生日,\n" +
                "DATE_ADD(STR_TO_DATE(right(left(d.idCard,14),8),\"%Y%m%d\"),INTERVAL 56 YEAR) as 超龄日期,\n" +
                "d.cancelSystem as cancelSystem,\n" +
                "DATE_FORMAT(c.beginDate,'%Y-%m-%d') as 本次采浆日期, \n" +
                "DATE_FORMAT(d.plasmaDate,'%Y-%m-%d')  as 最后采浆日期 ,\n" +
                "l.linkmanName as 车主,\n" +
                "d.remark from t_bims_donor_register r \n" +
                "left join t_bims_plasma_collection c on r.donorId = c.donorId and LEFT(r.registerDate,8) = LEFT(c.beginDate,8) \n" +
                "left join t_bims_donor d on c.donorId = d.donorId \n" +
                "left join t_bims_linkman l on r.linkmanId = l.linkmanId \n" +
                "left join t_bims_code_area as area on area.ccode = d.codeArea\n" +
                "left join t_bims_code_area as quxian on quxian.ccode = left(d.codeArea,25)\n" +
                "where d.plasmaDate between ? and ? and d.collectionNumber between ? and ? and c.weight > 0 and  d.codeDonorState = 'N'\n" +
                ") dd where dd.本次采浆日期 = 最后采浆日期 and (dd.超龄日期 > now() or dd.超龄日期 is null)",
                new Object[] {DateUtil.format(dateRangeStart,"yyyyMMddHHmmsss"),DateUtil.format(dateRangeEnd,"yyyyMMddHHmmsss"),collectionNumberStart,collectionNumberEnd}), HashMap.class);

        Integer memberSize = memberList.size();
        Integer donorSize = donorList.size();
        double size = Math.ceil(donorSize / memberSize);

        int memberCount = 1;
        int yushu = (int)(donorSize - memberSize * size);
        for (Iterator<Map<String, String>> iterator = memberList.iterator(); iterator.hasNext();) {
            int thisSize = (int)size;
            if (memberCount <= yushu) {
                thisSize = thisSize + 1;
            }
            if (donorList.size() == 0) {
                break;
            }
            Map<String, String> m = iterator.next();
            System.out.println(m.get("name"));
            String name = m.get("name");
            String 科室 = m.get("keshi");

            List memberDonorList = new ArrayList();

            for (int i = 0; i < thisSize; i++) {
                if (donorList.size() == 0) {
                    break;
                }
                //随机往员工的List里放一个浆员
                Random random = new Random();
                HashMap donorMap = donorList.get(0);
                donorMap.put("memberName", name);
                memberDonorList.add(donorMap);

                donorChangeList.add(donorMap);

                donorList.remove(0);
            }

            Map data = new HashMap();
            data.put("name",name);
            data.put("dateRange",dateRange);
            data.put("collectionNumber",collectionNumberStart + "至" + collectionNumberEnd);
            data.put("today",DateUtil.format(new Date(), "yyyy-MM-dd"));
            data.put("donorList", memberDonorList);
            data.put("count", memberDonorList.size());
            tongjiList.add(data);

            if (memberDonorList.size() == 0) {
                continue;
            }
            ManagerFactory.createExcel(root,
                    "excelTemplates/员工/超半年多年回访名单-1次.xls",
                    "\\"+ 科室 + "-" + name + "-超半年回访名单（" + memberDonorList.size() + "）.xls",data);
            memberCount = memberCount + 1;
        }

        if (donorChangeList.size() > 0) {
            Map data = new HashMap();
            data.put("name","全部");
            data.put("dateRange",dateRange);
            data.put("collectionNumber",collectionNumberStart + "至" + collectionNumberEnd);
            data.put("today",DateUtil.format(new Date(), "yyyy-MM-dd"));
            data.put("donorList", donorChangeList);
            data.put("count", donorChangeList.size());
            tongjiList.add(data);

            ManagerFactory.createExcel(root,
                    "excelTemplates/员工/超半年多年回访名单-1次-汇总.xls",
                    "\\0-全部-超半年回访名单（" + donorChangeList.size() + "）.xls",data);
        }
    }
}
